package com.estore.dao;

import com.estore.entity.Product;
import com.estore.util.DBConnection;

import java.sql.*;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;


public class ProductDao {
    private Connection conn = null; // 定义连接的对象
    private PreparedStatement ps = null; // 定义预准备的对象

    public ProductDao() {
        conn = DBConnection.getConnection();
    }

    // 全部查询
    public List<Product> findAll() {
        try {
            ps = conn.prepareStatement("select * from tb_product order by id DESC");
            ResultSet rs = ps.executeQuery();
            return rs2List(rs);
        } catch (SQLException ex) {
            System.err.println("SQL 语句有误");
            ex.printStackTrace();
            return Collections.emptyList();
        }
    }

    // 以商品的是否新品/特价为条件查询信息 0 新品 1 特价商品
    public List<Product> findByDiscount(Integer discount) {// discount为1:特价商品
        // 0:新品
        List<Product> list = new ArrayList<>();
        try {
            ps = conn.prepareStatement("select * from tb_product where discount=? ");
            ps.setInt(1, discount);
            ResultSet rs = ps.executeQuery();
            return rs2List(rs);
        } catch (SQLException ex) {
            ex.printStackTrace();
            System.out.println("数据库访问失败");
            return Collections.emptyList();
        }
    }

    // 以商品的名称为搜索条件查询所有商品
    public List<Product> selectProductsSearch(String search) {
        List<Product> list = new ArrayList<Product>();
        try {
            String strSql = "select * from tb_product where name like  '%" + search.trim() + "%' order by id DESC";
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery(strSql);
            list = rs2List(rs);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return list;
    }

    // 以商品的编号为条件查询单个商品 信息
    public Product selectOneProducts(int id) {
        Product e = new Product();
        try {
            ps = conn.prepareStatement("select * from tb_product where id=? order by id DESC");
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            return rs2List(rs).get(0);
        } catch (SQLException ex) {
            ex.printStackTrace();
            System.out.println("SQL语句有误");
            return null;
        }
    }

    // 以商品编号查询商品名称
    public String selectOneNameByProductId(int id) {
        String name = null;
        try {
            ps = conn
                    .prepareStatement("select * from tb_product where id=?");
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                name = rs.getString(4);
            }
        } catch (SQLException ex) {
        }
        return name;
    }

    // 根据商品的ID修改商品的销售数量
    public boolean updateAProductSoldNumber(int productAmount, int id) {
        try {
            ps = conn .prepareStatement("update tb_product set product_amount=product_amount+? where id=?");
            ps.setInt(1, productAmount);
            ps.setInt(2, id);
            ps.executeUpdate();
            ps.close();
            return true;
        } catch (SQLException ex) {
            System.out.println("更新数据表商品销量失败！");
            return false;
        }
    }

    // 以商品的大类别的编号为条件查询信息
    public List selectProductByCatagoryMain(int big) {
        List list = new ArrayList();
        try {
            if (big == 0 || big == -1) {
                ps = conn
                        .prepareStatement("select * from tb_product order by id DESC");
            } else {
                ps = conn
                        .prepareStatement("select * from tb_product where category_main_id=? order by id DESC");
                ps.setInt(1, big);
            }
            ResultSet rs = ps.executeQuery();
            list = rs2List(rs);

        } catch (SQLException ex) {
        }
        return list;
    }

    // 查询销售排行
    public List<Product> selectProductsRank() {
        try {
            // 查询商品销量前七位的相关商品资料
            ps = conn.prepareStatement("select * from tb_product order by product_amount DESC limit 6");
            ResultSet rs = ps.executeQuery();
            return rs2List(rs);
        } catch (SQLException ex) {
            return Collections.emptyList();
        }
    }

    // ResultSet类型数据转制为List,其中元素为 Product
    public List<Product> rs2List(ResultSet rs) {
        List<Product> list = new ArrayList<>();
        try {
            while (rs.next()) {
                Product e = new Product();
                e.setId(rs.getInt(1));
                e.setCategoryMainId(rs.getInt(2));
                e.setCategoryBranchId(rs.getInt(3));
                e.setName(rs.getString(4));
                e.setProducingArea(rs.getString(5));
                e.setDescription(rs.getString(6));
                e.setCreateTime(rs.getString(7));
                e.setMarketPrice(rs.getFloat(8));
                e.setSellPrice(rs.getFloat(9));
                e.setProductAmount(rs.getInt(10));
                e.setPicture(rs.getString(11));
                e.setDiscount(rs.getInt(12));
                list.add(e);
            }
        } catch (SQLException ex) {
            System.out.println("Rs转换为List失败！");
        }
        return list;
    }

}
